package etr.bk.db;

import java.awt.EventQueue;
import java.awt.Graphics2D;
import java.awt.Image;
import java.awt.image.BufferedImage;
import java.io.BufferedInputStream;
import java.io.BufferedOutputStream;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.io.Reader;
import java.io.Writer;
import java.lang.instrument.Instrumentation;
import java.sql.Blob;
import java.sql.Clob;
import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Types;
import java.util.ArrayList;
import java.util.Iterator;
import java.util.concurrent.Callable;
import java.util.concurrent.ExecutionException;
import java.util.concurrent.FutureTask;

import javax.imageio.ImageIO;
import javax.swing.JProgressBar;

import org.h2.util.New;

import etr.bk.gui.MainListener;

public class WorkDBInfo
{
	private static WorkDBInfo singl = null;
	private static Connection conn = null;
	
	private WorkDBInfo(Connection connect)
	{
		this.conn = connect;
		
		this.createTableUS();
		this.createTableBLOB();
		this.createTableCLOB();
		this.createDfltImg();
		
		this.createTrigger();
	}

	public static WorkDBInfo getDBSinglTone(Connection con)
	{
		if (WorkDBInfo.singl == null)
		{
			WorkDBInfo.singl = new WorkDBInfo(con);
			return WorkDBInfo.singl;
		}

		return WorkDBInfo.singl;

	}

	// -----------------------------------------------------------------

	private void createTableCLOB()
	{
		DatabaseMetaData metaDataDB = null;
		ResultSet rs = null;
		Statement stat = null;

		try
		{
			metaDataDB = conn.getMetaData();

			rs = metaDataDB.getTables(null, null, "CLOBTABL", null);

			if (!rs.next())
			{
				System.out.println("Create Table for clob");
				stat = conn.createStatement();
				stat.executeUpdate("CREATE TABLE CLOBTABL (id int identity PRIMARY KEY, obj CLOB)");
				stat.close();
			}
			rs.close();

		}
		catch (SQLException e)
		{

			e.printStackTrace();
		}
		finally
		{
			try
			{
				rs.close();
			}
			catch (SQLException e)
			{
				System.out.println("Close dont in CLOB Create");
				e.getMessage();
			}
		}
	}

	private void createTableBLOB()
	{
		DatabaseMetaData metaDataDB = null;
		ResultSet rs = null;
		Statement stat = null;

		try
		{
			metaDataDB = conn.getMetaData();

			rs = metaDataDB.getTables(null, null, "BLOBTABL", null);

			if (!rs.next())
			{
				System.out.println("Create Table for blob");
				stat = conn.createStatement();
				stat.executeUpdate("CREATE TABLE BLOBTABL (id int identity PRIMARY KEY, obj BLOB, USID int, smlobj BLOB)");
				stat.close();
			}
			rs.close();

		}
		catch (SQLException e)
		{

			e.printStackTrace();
		}
		finally
		{
			try
			{
				rs.close();
			}
			catch (SQLException e)
			{
				System.out.println("Close dont in BLOB Create");
				e.getMessage();
			}
		}

	}

	private void createTableUS()
	{
		DatabaseMetaData metaDataDB = null;
		ResultSet rs = null;
		Statement stat = null;

		String createTableUSsql = "CREATE TABLE US (id int identity PRIMARY KEY, bid int, cid int, name VARCHAR(30), surname VARCHAR(30), birthday VARCHAR(20), country VARCHAR(30), city VARCHAR(30),	address VARCHAR(100), work VARCHAR(50), mail VARCHAR(40), phone VARCHAR(15),isq VARCHAR(15), skype VARCHAR(30),sex VARCHAR(6))";

		try
		{
			metaDataDB = conn.getMetaData();

			rs = metaDataDB.getTables(null, null, "US", null);

			if (!rs.next())
			{
				System.out.println("Create Table for User data");
				stat = conn.createStatement();
				stat.executeUpdate(createTableUSsql);
				stat.close();
			}
			rs.close();
		}
		catch (SQLException e)
		{
			e.printStackTrace();
		}
		finally
		{
			try
			{
				rs.close();
			}
			catch (SQLException e)
			{
				System.out.println("Close dont in US Create");
				e.getMessage();
			}
		}
	}
	
	private void createDfltImg()
	{
		DatabaseMetaData metaDataDB = null;
		ResultSet rs = null;
		Statement stat = null;

		String createTableUSsql = "CREATE TABLE DEFLTIMGTABLE (DEFOBJ BLOB)";

		try
		{
			metaDataDB = conn.getMetaData();

			rs = metaDataDB.getTables(null, null, "DEFLTIMGTABLE", null);

			if (!rs.next())
			{
				System.out.println("Create Table for Default Tabl IMG");
				stat = conn.createStatement();
				stat.executeUpdate(createTableUSsql);
				stat.close();
			}
			rs.close();
		}
		catch (SQLException e)
		{
			e.printStackTrace();
		}
		finally
		{
			try
			{
				rs.close();
			}
			catch (SQLException e)
			{
				System.out.println("Close dont in DEFLTIMGTABLE Create");
				e.getMessage();
			}
		}
	}
	
	private void createTrigger()
	{
		ResultSet rs = null;
		PreparedStatement pstat = null;
		
		String selectForTrigger = "SELECT TRIGGER_NAME FROM INFORMATION_SCHEMA.TRIGGERS WHERE TRIGGER_NAME = ?";
		String createTrigger = "CREATE TRIGGER IF NOT EXISTS TrigForDefltImg BEFORE INSERT ON DefltImgTable CALL \"etr.bk.db.TriggerForDefault\"";
		
		try {
			
			pstat = conn.prepareStatement(selectForTrigger);
			
			pstat.setString(1, "TrigForDefltImg");
			rs = pstat.executeQuery();
			
			if (!rs.next()){
				pstat.close();
				pstat = conn.prepareStatement(createTrigger);
				pstat.executeUpdate();
			}
			else {
				return;
			}
		}
		catch (SQLException e) {
			e.printStackTrace();
		}
		finally {
			
			try {
				rs.close();
				pstat.close();
			} 
			catch (SQLException e) {
				e.printStackTrace();
			}
		}		
	}

	// -----------------------------------------------------------------

	public BufferedImage getImgFromBlobTbl(int id)
	{ // id in US table
		BufferedImage img;

		String selectExp = "SELECT OBJ FROM BLOBTABL as t WHERE t.ID = ?";
		PreparedStatement pstat = null;
		ResultSet rs = null;

		try
		{
			pstat = this.conn.prepareStatement(selectExp);
			pstat.setInt(1, id);
			rs = pstat.executeQuery();
			Blob blobobj = this.conn.createBlob();

			if (rs.next())
			{
				blobobj = rs.getBlob(1);
			}
			else
			{
				System.out.println("Image whis id = " + id + " -- not exists");
				return null;
			}

			InputStream blobInpStream = blobobj.getBinaryStream();
			img = ImageIO.read(blobInpStream);

			System.out.println(img.toString());

			return img;
		}
		catch (SQLException e)
		{

			e.printStackTrace();
			return null;
		}
		catch (IOException e)
		{
			e.printStackTrace();
			return null;
		}

	}
	
	private BufferedImage getDefaultImage(){
		PreparedStatement pstat = null;
		ResultSet rs = null;
		BufferedImage rezImage = null;
		Blob blobObj = null;
		InputStream blobInpStream = null;
		String selectImage = "SELECT DEFOBJ FROM DEFLTIMGTABLE";
		
		try {
			blobObj = this.conn.createBlob();
			pstat = this.conn.prepareStatement(selectImage);
			rs = pstat.executeQuery();
			
			
			rs.next();
			if (!rs.wasNull())	{
				blobObj = rs.getBlob(1); 
				if (blobObj != null) { 
					blobInpStream = blobObj.getBinaryStream();
					rezImage = ImageIO.read(blobInpStream);
				}
				else {
					rezImage = null;
				}
			}
			else {
				rezImage = null;
			}
			return rezImage;
		}
		catch (SQLException e) {
			e.printStackTrace();
		}
		catch (IOException e) {
			e.printStackTrace();
		}
		finally {
			try {
				rs.close();
				pstat.close();
			}
			catch (SQLException e) {
				e.printStackTrace();
			}
			
		}
		
		return null;
		
	}

	@SuppressWarnings("finally")
	public ArrayList<WrapperLPan> getInfoForLPan()
	{
		BufferedImage img;
		String nameFam;
		int id;

		ArrayList<WrapperLPan> array = new ArrayList<WrapperLPan>();

		String selectExp = "SELECT u.id, u.name ,u.surname, b.smlobj FROM us u  LEFT OUTER JOIN blobtabl b ON  u.bid = b.id";

		PreparedStatement pstat = null;
		ResultSet rs = null;
		InputStream blobInpStream = null;

		try
		{
			pstat = this.conn.prepareStatement(selectExp);
			rs = pstat.executeQuery();

			Blob blobImg = this.conn.createBlob();

			while (rs.next())
			{

				blobImg = rs.getBlob(4);
				if (rs.wasNull() != true)
				{
					blobInpStream = blobImg.getBinaryStream();
					img = ImageIO.read(blobInpStream);
				}
				else {
					img = this.getDefaultImage();
				}

				nameFam = rs.getString(2) + " " + rs.getString(3);

				id = rs.getInt(1);

				array.add(new WrapperLPan(id, img, nameFam));
			}

			return array;

		}
		catch (SQLException e)
		{
			array = null;
			e.printStackTrace();
		}
		catch (IOException e)
		{
			array = null;
			e.printStackTrace();
		}
		catch (NullPointerException e)
		{
			e.printStackTrace();
			System.out.println("sakdfjasklghjkasdfsa;ldkflas;dfkl;askfd;");
		}
		finally
		{
			try
			{
				rs.close();
				pstat.close();
			}
			catch (SQLException e)
			{
				e.printStackTrace();
			}
			return array;
		}
	}
	
	@SuppressWarnings("finally")
	public ArrayList<WrapperLPan> getInfoForUnloader()
	{
		String nameFam;
		int id;

		ArrayList<WrapperLPan> array = new ArrayList<WrapperLPan>();

		String selectExp = "SELECT u.id, u.name ,u.surname FROM us u  LEFT OUTER JOIN blobtabl b ON  u.bid = b.id";

		PreparedStatement pstat = null;
		ResultSet rs = null;
		InputStream blobInpStream = null;

		try
		{
			pstat = this.conn.prepareStatement(selectExp);
			rs = pstat.executeQuery();

			while (rs.next())
			{
				nameFam = rs.getString(2) + " " + rs.getString(3);

				id = rs.getInt(1);

				array.add(new WrapperLPan(id, null, nameFam));
				System.out.println("For UNLOADER IN OBJDB ");
			}

			return array;

		}
		catch (SQLException e)
		{
			array = null;
			e.printStackTrace();
		}
		catch (NullPointerException e)
		{
			e.printStackTrace();
			System.out.println("sakdfjasklghjkasdfsa;ldkflas;dfkl;askfd;");
		}
		finally
		{
			try
			{
				rs.close();
				pstat.close();
				System.out.println("Close     !!!!");
			}
			catch (SQLException e)
			{
				e.printStackTrace();
			}
			return array;
		}
	}
	
	
	@SuppressWarnings("finally")
	public WrapperRPanInfo getInfoForRpanInfo(Integer iden)
	{
		if (iden == null) return null;
		
		WrapperRPanInfo wrapper = null;

		ArrayList<String> array = new ArrayList<String>();
		BufferedImage img = null;
		String notes = null;

		String selectExp = "SELECT  u.name, u.surname ,u.birthday ,u.country, u.city , u.address , u.work , u.mail , u.phone ,u.isq , u.skype ,u.sex, b.obj, c.obj FROM (US u LEFT OUTER JOIN BLOBTABL b ON  u.bid  = b.id) LEFT OUTER JOIN CLOBTABL c ON u.cid = c.id WHERE  u.id = ?";

		PreparedStatement pstat = null;
		ResultSet rs = null;
		InputStream blobInpStream = null;
		Reader clobInStrim = null;
		char[] buff;

		try
		{
			pstat = this.conn.prepareStatement(selectExp);
			pstat.setInt(1, iden);
			rs = pstat.executeQuery();

			if (rs.next() != true)
				return null;

			for (int i = 1; i <= 12; i++)
			{
				array.add(rs.getString(i));
			}

			Blob blobImg = this.conn.createBlob();
			blobImg = rs.getBlob(13);
			if (rs.wasNull() != true)
			{
				blobInpStream = blobImg.getBinaryStream();
				img = ImageIO.read(blobInpStream);
			}
			else {
				img = this.getDefaultImage();
			}

			Clob clobobj = this.conn.createClob();
			clobobj = rs.getClob(14);
			if (rs.wasNull() != true)
			{
				long len = clobobj.length();

				buff = new char[(int) len];

				clobInStrim = clobobj.getCharacterStream();
				clobInStrim.read(buff);
				clobInStrim.close();

				notes = new String(buff);
			}
			else
				notes = null;

			wrapper = new WrapperRPanInfo(img, array, notes);

			return wrapper;

		}
		catch (SQLException e)
		{

			e.printStackTrace();
		}
		catch (IOException e)
		{

			e.printStackTrace();
		}
		finally
		{
			try
			{
				rs.close();
				pstat.close();
			}
			catch (SQLException e)
			{
				e.printStackTrace();
			}
			return wrapper;
		}
	}
	
	//--------------------------------------------------------------------------------
	
	@SuppressWarnings("finally")
	private Integer setImgIntoDB(Integer id, BufferedImage imag)
	{// id from us
		Integer ident = id;
		Integer rezult = null;
		BufferedImage img = imag;
		PreparedStatement pstat = null;
		ResultSet rs = null;

		int bufImgType = 0;
		Image image = null;
		
		String insertExp;
		String selectForUpdt = null;
		
		if (ident == null)
		{
			insertExp = "INSERT INTO BLOBTABL (OBJ,SMLOBJ) VALUES (?,?)";
		}
		else
		{
			insertExp = "UPDATE BLOBTABL SET OBJ = ?, SMLOBJ = ? WHERE ID = SELECT bid  FROM US WHERE ID = ?";
			selectForUpdt = "SELECT bid  FROM US WHERE ID = ?";
		}

		try
		{

			if (img != null)
			{

				Blob blobobj = conn.createBlob();
				Blob blbobPrev = conn.createBlob();
				OutputStream blobOutStrim = blobobj.setBinaryStream(1);

				boolean succesWrit = ImageIO.write(img, "jpg", blobOutStrim);

				blobOutStrim.flush();
				blobOutStrim.close();
				
				//-------Preview------------------------------
				blobOutStrim = blbobPrev.setBinaryStream(1);
				
				bufImgType = img.getType();
				image = img.getScaledInstance(300, 300, Image.SCALE_DEFAULT);
				img = new BufferedImage(300, 300, bufImgType);
				Graphics2D g2 = img.createGraphics();
				g2.drawImage(image,null,null);
				g2.dispose();
				
				ImageIO.write(img, "jpg", blobOutStrim);
				
				blobOutStrim.flush();
				blobOutStrim.close();
				//---------------------------------------------
				
				pstat = this.conn.prepareStatement(insertExp);
				pstat.setBlob(1, blobobj);
				pstat.setBlob(2, blbobPrev);
				if (ident == null)
				{
					pstat.executeUpdate();
					rs = pstat.getGeneratedKeys();
				}
				else
				{
					pstat.setInt(3, ident.intValue());
					pstat.executeUpdate();
					pstat.close();

					pstat = this.conn.prepareStatement(selectForUpdt);
					pstat.setInt(1, ident.intValue());
					rs = pstat.executeQuery();
				}

				if (rs.next() == true)
					rezult = rs.getInt(1);
				else
					rezult = null;

				return rezult;

			}
			else
			{
				if (id != null)
				{

					pstat = this.conn.prepareStatement(selectForUpdt);
					pstat.setInt(1, ident.intValue());
					rs = pstat.executeQuery();

					if (rs.next() == true)
					{
						rezult = rs.getInt(1);
						if (rs.wasNull())
							rezult = null;
					}
					else
						rezult = null;

					return rezult;
				}
				else
				{
					return null;
				}
			}

		}
		catch (SQLException e)
		{
			e.getMessage();
			e.printStackTrace();
		}
		catch (FileNotFoundException e)
		{
			e.printStackTrace();
		}
		catch (IOException e)
		{
			e.printStackTrace();
		}
		finally
		{
			try
			{
				if (pstat != null)
					pstat.close();
			}
			catch (SQLException e)
			{
				e.printStackTrace();
			}
			return rezult;
		}

	}
	
	@SuppressWarnings("finally")
	private Integer setNotesIntoDB(Integer id, String str)
	{// id in US table
		Integer ident = id;
		Integer rezult = null;
		PreparedStatement pstat = null;
		ResultSet rs = null;

		String insertExp;
		String selectForUpdt = null;
		if (ident == null)
		{
			insertExp = "INSERT INTO CLOBTABL (OBJ) VALUES (?)";
		}
		else
		{
			insertExp = "UPDATE CLOBTABL SET OBJ = ? WHERE ID = SELECT cid  FROM US WHERE ID = ?";
			selectForUpdt = "SELECT cid  FROM US WHERE ID = ?";
		}

		try
		{

			if (str != null)
			{
				// this.conn.setAutoCommit(false); // for transaction

				Clob clobobj = this.conn.createClob();
				Writer ClobOutStrim = clobobj.setCharacterStream(1);

				ClobOutStrim.write(str);

				ClobOutStrim.flush();
				ClobOutStrim.close();

				pstat = this.conn.prepareStatement(insertExp);
				pstat.setClob(1, clobobj);

				if (ident == null)
				{
					pstat.executeUpdate();
					rs = pstat.getGeneratedKeys();
				}
				else
				{
					pstat.setInt(2, ident.intValue());
					pstat.executeUpdate();
					pstat.close();

					pstat = this.conn.prepareStatement(selectForUpdt);
					pstat.setInt(1, ident.intValue());
					rs = pstat.executeQuery();

				}

				if (rs.next() == true)
					rezult = rs.getInt(1);
				else
					rezult = null;

				return rezult;

			}
			else
			{
				if (id != null)
				{

					pstat = this.conn.prepareStatement(selectForUpdt);
					pstat.setInt(1, ident.intValue());
					rs = pstat.executeQuery();

					if (rs.next() == true)
					{
						rezult = rs.getInt(1);
						if (rs.wasNull())
							rezult = null;
					}
					else
						rezult = null;

					return rezult;
				}
				else
				{
					return null;
				}
			}

		}
		catch (SQLException e)
		{
			e.getMessage();
			e.printStackTrace();
		}
		catch (FileNotFoundException e)
		{
			e.printStackTrace();
		}
		catch (IOException e)
		{
			e.printStackTrace();
		}
		finally
		{
			try
			{
				if (pstat != null)
					pstat.close();
			}
			catch (SQLException e)
			{
				e.printStackTrace();
			}

			return rezult;
		}

	}

	private void setInfoIntoDB(Integer id, ArrayList<String> strItems,
			Integer blb, Integer clb)
	{// before do this method you must execute above two methods(and get blb,
		// clb)
		Integer ident = id;

		PreparedStatement pstat = null;
		ResultSet rs = null;
		String insertInfo;

		if (ident == null)
		{
			insertInfo = "INSERT INTO US (bid, cid, name, surname, birthday, country, city, address, work, mail, phone, isq, skype, sex) VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?)";
		}
		else
		{
			insertInfo = "UPDATE US SET bid = ?, cid = ?, name = ?, surname = ?, birthday = ?, country = ?, city = ?, address = ?, work = ?, mail = ?, phone =?, isq = ?, skype = ?, sex = ? WHERE id = ?";
		}

		try
		{
			pstat = this.conn.prepareStatement(insertInfo);

			int i = 1;

			if (blb != null)
				pstat.setInt(i++, blb.intValue());
			else
				pstat.setNull(i++, Types.NULL);

			if (clb != null)
				pstat.setInt(i++, clb.intValue());
			else
				pstat.setNull(i++, Types.NULL);

			for (String str : strItems)
			{
				pstat.setString(i++, str);
			}

			if (ident != null)
				pstat.setInt(i, ident.intValue());

			pstat.executeUpdate();

			rs = pstat.getGeneratedKeys();

			if (rs.next() != true)
				System.out.println("*********INFA V DB NOT INSERTED!!*******");

		}
		catch (SQLException e)
		{

			e.printStackTrace();
		}
		finally
		{

			try
			{
				pstat.close();
				rs.close();
			}
			catch (SQLException e)
			{
				e.printStackTrace();
			}

		}
	}
	
	public void setImgIntoDfltTbl(BufferedImage image){
		
		PreparedStatement pstat = null;
		ResultSet rs = null;
		String insertDfltImg = "INSERT INTO DEFLTIMGTABLE (DEFOBJ) VALUES (?)";
		
		try {
			
			conn.setAutoCommit(false);
			pstat = conn.prepareStatement(insertDfltImg);
			
			if (image != null){
				Blob blobobj = this.conn.createBlob();
				OutputStream blobOutStrim = blobobj.setBinaryStream(1);
				boolean succesWrit = ImageIO.write(image, "jpg", blobOutStrim);
				blobOutStrim.flush();
				blobOutStrim.close();
				
				pstat.setBlob(1, blobobj);
			}
			else {
				pstat.setNull(1, Types.NULL);
			}
			
			pstat.executeUpdate();
			
			conn.commit();
			conn.setAutoCommit(true);
		} 
		catch (SQLException e) {
			e.printStackTrace();
		} 
		catch (IOException e) {
			e.printStackTrace();
		}
		finally {
			try {
				pstat.close();
			} 
			catch (SQLException e) {
				e.printStackTrace();
			}
		}
		
		
	}
	
	
	//---------------------------------------------------------------------------------
	
	public void insertAllInfoIntoDB(ArrayList<String> strItems,
			BufferedImage image, String strNotes)
	{
		Integer imageID = null;
		Integer notesID = null;

		if (image != null)
			imageID = this.setImgIntoDB(null, image);

		if (strNotes != null)
			notesID = this.setNotesIntoDB(null, strNotes);

		this.setInfoIntoDB(null, strItems, imageID, notesID);
	}

	public void updateAllInfoIntoDB(int id, ArrayList<String> strItems,BufferedImage image, String strNotes)
	{
		System.out.println("***** Db  " + strNotes + "----id " + id);

		Integer imageFLG = 12;
		Integer notesFLG = 12;
		
		PreparedStatement pstat = null;
		ResultSet rs = null;
		
		//--------------------------------------------------------------------
		if (image == null)
		{
			String statDel = "DELETE FROM BLOBTABL b WHERE b.ID = SELECT u.BID FROM US u WHERE u.ID = ?";
			String statUpdateUS = "UPDATE US SET BID = NULL WHERE ID = ?";
			try 
			{
				pstat = this.conn.prepareStatement(statDel);
				pstat.setInt(1, id);
				pstat.executeUpdate();
				pstat.close();
				
				pstat = this.conn.prepareStatement(statUpdateUS);
				pstat.setInt(1, id);
				pstat.executeUpdate();
				pstat.close();
			} 
			catch (SQLException e) 
			{
				e.printStackTrace();
			}
		}
		
		if (strNotes == null)
		{
			String statDel = "DELETE FROM CLOBTABL b WHERE b.ID = SELECT u.CID FROM US u WHERE u.ID = ?";
			String statUpdateUS = "UPDATE US SET CID = NULL WHERE ID = ?";
			try 
			{
				pstat = this.conn.prepareStatement(statDel);
				pstat.setInt(1, id);
				pstat.executeUpdate();
				pstat.close();
				
				pstat = this.conn.prepareStatement(statUpdateUS);
				pstat.setInt(1, id);
				pstat.executeUpdate();
				pstat.close();
			} 
			catch (SQLException e) 
			{
				e.printStackTrace();
			}
		}
		// -------------------------------------------------------------------
		

		String selectExists = "SELECT u.BID , u.CID  FROM US u WHERE u.ID = ?";

		try
		{
			pstat = this.conn.prepareStatement(selectExists);
			pstat.setInt(1, id);

			rs = pstat.executeQuery();
			if (rs.next() != true)
				System.out.println("--- Pindeath  - in update inf!!!");

			rs.getInt(1);
			if (rs.wasNull())
				imageFLG = null;

			rs.getInt(2);
			if (rs.wasNull())
				notesFLG = null;

			pstat.close();

		}
		catch (SQLException e)
		{
			e.printStackTrace();
		}

		// --------------------------------------------------------------------

		Integer imageID = null;
		Integer notesID = null;

		if (imageFLG == null)
			imageID = this.setImgIntoDB(null, image);
		else
			imageID = this.setImgIntoDB(id, image);

		if (notesFLG == null)
			notesID = this.setNotesIntoDB(null, strNotes);
		else
			notesID = this.setNotesIntoDB(id, strNotes);

		this.setInfoIntoDB(id, strItems, imageID, notesID);
	}

	public void deleteRecord(int usID)
	{
		PreparedStatement pstat = null;

		String deleteFromBLOB = "DELETE FROM BLOBTABL b WHERE (b.ID = SELECT u.BID FROM US u WHERE u.ID = ?) OR b.USID = ?";
		String deleteFromCLOB = "DELETE FROM CLOBTABL c WHERE c.ID = SELECT u.CID FROM US u WHERE u.ID = ?";
		String deleteFromUS = "DELETE FROM US u WHERE u.ID = ?";

		try
		{
			pstat = this.conn.prepareStatement(deleteFromBLOB);
			pstat.setInt(1, usID);
			pstat.setInt(2, usID);
			pstat.executeUpdate();
			pstat.close();

			pstat = this.conn.prepareStatement(deleteFromCLOB);
			pstat.setInt(1, usID);
			pstat.executeUpdate();
			pstat.close();

			pstat = this.conn.prepareStatement(deleteFromUS);
			pstat.setInt(1, usID);
			pstat.executeUpdate();
			pstat.close();

		}
		catch (SQLException e)
		{
			e.printStackTrace();
		}
		finally
		{
			try
			{
				pstat.close();
			}
			catch (SQLException e)
			{
			}
		}

	}
	
}
